<?php
class Stats {
	
	public function __construct(&$mysql){
		$this->mysql = $mysql;
	}
	
	public function getTabMonth($n){
		for($i=$n;$i>=0;$i--){
			$tabMonth[] = date("Y-m",mktime(0,0,0,date("m")-$i,date("d"),date("Y")));
		}
		return $tabMonth;
	}
	
	public function nb_devis(){
		$mysql = $this->mysql;
		$query = "SELECT date,_id from devis ORDER BY date ASC;";
		$res = $mysql->query($query);
		if($mysql->errno != 0) throw new Exception ("Erreur MySQL : ".$mysql->error);
		while($line = $res->fetch_object()){
			$query2 = "SELECT _id FROM facture WHERE devis_id = $line->_id;";
			$res2 = $mysql->query($query2);
			if($res2->num_rows == 1){
				$stats_facture[substr($line->date,0,7)]++;
			}
			$stats_devis[substr($line->date,0,7)]++;
		}
		$js1 = "[";
		$js2 = "[";
		$months = $this->getTabMonth(12);
		foreach($months as $date){
			list($y,$m) = explode("-",$date);
			if(isset($stats_devis[$date])){
				$js1 .= "['$m-$y',".$stats_devis[$date]."],";
			}else{
				$js1 .= "['$m-$y',0],";
			}
			if(isset($stats_facture[$date])){
				$js2 .= "['$m-$y',".$stats_facture[$date]."],";
			}else{
				$js2 .= "['$m-$y',0],";
			}
		}
		$js1 = substr($js1,0,-1)."]";
		$js2 = substr($js2,0,-1)."]";
		return array($js1,$js2);
	}
	public function ca_mensuel(){
		$mysql = $this->mysql;
		$query = "SELECT paiement_client.date, montant_ht FROM facture INNER JOIN paiement_client ON paiement_client._facture = facture._id WHERE facture.user_id = ".$_SESSION["user_id"]." ORDER BY paiement_client.date ASC;";
		$res = $mysql->query($query);
		if($mysql->errno != 0) throw new Exception ("Erreur MySQL : ".$mysql->error);
		while($line = $res->fetch_object()){
			$tab[substr($line->date,0,7)] += $line->montant_ht;
		}
		$js = "[";
		$months = $this->getTabMonth(12);
		foreach($months as $date){
			list($y,$m) = explode("-",$date);
			if(isset($tab[$date])){
				$js .= "['$m-$y',".$tab[$date]."],";
//				$js .= $tab[$date].",";
			}else{
				$js .= "['$m-$y',0],";
//				$js .= "0,";
			}
		}
		$js = substr($js,0,-1)."]";
		return $js;
	}
	public function balance(){
		$mysql = $this->mysql;
		
		$query = "SELECT montant_ht, paiement_client.date as date FROM facture INNER JOIN paiement_client ON paiement_client._facture = facture._id WHERE user_id = ".$_SESSION["user_id"].";";
		$res = $mysql->query($query);
		if($mysql->errno != 0) throw new Exception ("Erreur MySQL : ".$mysql->error);
		while($line = $res->fetch_object()){
			$tab[substr($line->date,0,7)] += $line->montant_ht;
		}
		$js = "[";
		$months = $this->getTabMonth(12);
		foreach($months as $date){
			list($y,$m) = explode("-",$date);
			if(isset($tab[$date])){
				$js .= "['$m-$y',".$tab[$date]."],";
			}else{
				$js .= "['$m-$y',0],";
			}
		}
		$js = substr($js,0,-1)."]";
		
		$query = "SELECT montant_ttc, paiement_fournisseur.date as date "
				."FROM facture_fournisseur INNER JOIN paiement_fournisseur ON paiement_fournisseur._facture = facture_fournisseur._id "
				."WHERE user_id = ".$_SESSION["user_id"].";";
		$res = $mysql->query($query);
		if($mysql->errno != 0) throw new Exception ("Erreur MySQL : ".$mysql->error);
		while($line = $res->fetch_object()){
			$tab2[substr($line->date,0,7)] += $line->montant_ttc;
		}
		$js2 = "[";
		$months = $this->getTabMonth(12);
		foreach($months as $date){
			list($y,$m) = explode("-",$date);
			if(isset($tab2[$date])){
				$js2 .= "['$m-$y',".$tab2[$date]."],";
			}else{
				$js2 .= "['$m-$y',0],";
			}
		}
		$js2 = substr($js2,0,-1)."]";
		
		return array($js,$js2);
	}
	public function achats(){
		
		$mysql = $this->mysql;
		
		$query = "SELECT montant_ttc, facture_fournisseur.date "
				."FROM facture_fournisseur INNER JOIN paiement_fournisseur "
				."ON facture_fournisseur._id = paiement_fournisseur._facture "
				."WHERE facture_fournisseur.user_id = '".$_SESSION["user_id"]."';";
		$res = $mysql->query($query);
		if($mysql->errno != 0) throw new Exception ("Erreur MySQL : ".$mysql->error);
		while($line = $res->fetch_object()){
			$tab[substr($line->date,0,7)] += $line->montant_ttc;
		}
		$js = "[";
		$months = $this->getTabMonth(12);
		foreach($months as $date){
			list($y,$m) = explode("-",$date);
			if(isset($tab[$date])){
				$js .= "['$m-$y',".$tab[$date]."],";
			}else{
				$js .= "['$m-$y',0],";
			}
		}
		$js = substr($js,0,-1)."]";
		return $js;
	}
	public function bestCustomer(){
		$mysql = $this->mysql;
		$query = "SELECT contact_id,nom,prenom,societe, sum(montant_ht) as total "
				."FROM facture, paiement_client, contacts "
				."WHERE facture._id = paiement_client._facture "
				."AND facture.contact_id = contacts._id "
				."AND facture.user_id = '".$_SESSION["user_id"]."' "
				."GROUP BY contact_id "
				."ORDER BY total DESC "
				."LIMIT 10";
		$res = $mysql->query($query);
		if($mysql->errno != 0) throw new Exception ("Erreur MySQL : ".$mysql->error);
		$js = "[";
		while($line = $res->fetch_object()){
			if($line->societe != ""){
				$nom = $line->societe;
			}else{
				$nom = trim($line->prenom." ".$line->nom);
			}
			$js .= "['$nom',$line->total],";
		}
		$js = substr($js,0,-1)."]";
		return $js;
	}
	public function bestFournisseur(){
		$mysql = $this->mysql;
		$query = "SELECT _fournisseur, nom, prenom, societe, sum(montant_ttc) as total "
				."FROM facture_fournisseur, fournisseur,paiement_fournisseur "
				."WHERE facture_fournisseur._id = paiement_fournisseur._facture "
				."AND facture_fournisseur._fournisseur = fournisseur._id "
				."AND facture_fournisseur.user_id = '".$_SESSION["user_id"]."' "
				."GROUP BY _fournisseur "
				."ORDER BY total DESC "
				."LIMIT 10";
		$res = $mysql->query($query);
		if($mysql->errno != 0) throw new Exception ("Erreur MySQL : ".$mysql->error);
		$js = "[";
		while($line = $res->fetch_object()){
			$nom = trim($line->prenom." ".$line->nom." ".$line->societe);
			$js .= "['$nom',$line->total],";
		}
		$js = substr($js,0,-1)."]";
		return $js;
	}
	/**
	 * Retourne le montant du CA pour l'année en cours (ou du mois)
	 */
	public function getCA($year="",$month=""){
		if($year == ""){
			$date = date("Y");
		}else{
			$date = $year;
		}
		if($month != ""){
			$date .= "-".$month;
		}
		$mysql = $this->mysql;
		$user_id = $_SESSION["user_id"];
		$query = "SELECT sum(montant_ht) as total "
				."FROM facture, paiement_client "
				."WHERE facture._id = paiement_client._facture "
				."AND facture.user_id = '$user_id' "
				."AND paiement_client.date LIKE \"".$date."%\" ";
		
		$res = $mysql->query($query);
		if($mysql->errno != 0) {
			throw new Exception ("Erreur MySQL : ".$mysql->error);
		}
		$line = $res->fetch_object();
		return ($line->total != "")?$line->total:"0.00";
	}
	/**
	 * Retourne le montant des charges de l'année en cours (ou du mois)
	 */
	public function getAchats($year="",$month=""){
		if($year == ""){
			$date = date("Y");
		}else{
			$date = $year;
		}
		if($month != ""){
			$date .= "-".$month;
		}
		$mysql = $this->mysql;
		$user_id = $_SESSION["user_id"];
		$query = "SELECT sum(montant_ttc) as total "
				."FROM facture_fournisseur , paiement_fournisseur "
				."WHERE facture_fournisseur._id = paiement_fournisseur._facture "
				."AND facture_fournisseur.user_id = '$user_id' "
				."AND facture_fournisseur.date LIKE \"".$date."%\"";
		$res = $mysql->query($query);
		$line = $res->fetch_object();
		return ($line->total != "")?$line->total:"0.00";
	}
	
}